package com.sample.javabean;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import com.sample.action.form.AreaForm;
import com.sample.jdbc.JdbcUtils;
import com.sample.util.Pager;

/**
 * 行政区域类
 * @author 深深
 * @version 1.0
 *
 */
public class Area {
	
	private String area_no;//区域编号
	private String area_name;//区域名称
	private int area_level;//级别
	private JdbcUtils jdbcUtils;
	/**
	 * 构造函数
	 */
	public Area(){
		jdbcUtils =new JdbcUtils();
	}
	
	
	public Area(String area_no, String area_name, int area_level) {
		super();
		this.area_no = area_no;
		this.area_name = area_name;
		this.area_level = area_level;
	}



	/**
	 * 下拉列表
	 * @return
	 */
	public List<Area> findAll()throws Exception{
		Connection conn=null;
		Statement stmt =null;
		ResultSet rs =null;
		List<Area> areaList =new ArrayList<Area>(20);
		try{
			 conn =jdbcUtils.getConn();
			 stmt =conn.createStatement();
			//3:构造SQL语句
			String sql="select * from t_area where 1=1";
			// 4:执行SQL并返回结果
			 rs =stmt.executeQuery(sql);
			while(rs.next()){
				String area_no = rs.getString("area_no");
				String area_name = rs.getString("area_name");
				int area_level = rs.getInt("area_level");
				Area area=new Area(area_no, area_name, area_level);
				//调用顺序列表的add方法，加入元素
				areaList.add(area);
				
			}
			
		}catch (Exception e) {
			e.printStackTrace();
			throw new Exception("获取信息失败，Exception_bean:" + e.getCause().getClass() + "," + e.getCause().getMessage());
			
		}finally{
			jdbcUtils.close(conn, stmt,rs);
		}
		return areaList;
	}
	
	/**
	 * 添加新政区域
	 * @param form
	 * @throws Exception
	 */
	public void addArea(AreaForm form)throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "insert into t_area(area_no,area_name,area_level) values('"
					    + form.getArea_no() + "','" +form.getArea_name()+"','"+form.getArea_level()+"')";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) {
			 e.printStackTrace();
			 throw new Exception("添加行政区域失败，Exception_bean:<--" + e+"->");
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		
	}
	
	
	/**
	 * 删除行政区域
	 * @throws Exception
	 */
	public void delArea()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "delete from t_area where area_no='"+this.area_no+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);
			// 5:关闭资源
		} catch (SQLException e) { 
			 e.printStackTrace();
			 throw new Exception("删除行政区域失败，Exception_bean:<--" + e+"->");
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		
	}
	
	
	
	/**
	 * 加载行政区域信息
	 * @throws Exception
	 */
	public void load()throws Exception{
		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null; 
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			// 3:构造插入SQL
			String sql = "select * from  t_area where area_no='"+this.area_no+"'";
			// 4:执行SQL并返回结果
			rs=stmt.executeQuery(sql);
			if(rs.next()){
				this.area_no = rs.getString("area_no");
				this.area_name = rs.getString("area_name");
				this.area_level = rs.getInt("area_level");
				
			}
			// 5:关闭资源
		} catch (SQLException e) {
			
			e.printStackTrace();
			throw new Exception("加载行政区域信息失败！Exception_bean:<--"+e+"-->"); 
		} finally {
			jdbcUtils.close(conn, stmt);
		}	
	}
	
	public void editArea(AreaForm form)throws Exception{
		Connection conn = null;
		Statement stmt = null;
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			if(form!=null){
			// 3:构造插入SQL
			String sql = "update  t_area set area_name='"+form.getArea_name()+"', area_level='"+form.getArea_level()+"' where area_no='"+form.getArea_no()+"'";
			// 4:执行SQL并返回结果
			stmt.executeUpdate(sql);}
			// 5:关闭资源
		} catch (SQLException e) {
			 
			
			e.printStackTrace();
			throw new Exception("编辑行政区域信息失败，Exception_bean:<--"+e+"-->");
			
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		
	}
	/**
	 * 分页显示行政区域
	 * @param page
	 * @param size
	 * @param form
	 * @return
	 * @throws Exception
	 */
	public Pager<Area> list(int page,int size,AreaForm form)throws Exception {

		Connection conn = null;
		Statement stmt = null;
		ResultSet rs =null;
		Pager<Area> areas=new Pager<Area>();
		areas.setPage(page);
		areas.setSize(size);
		try {
			conn = jdbcUtils.getConn();
			stmt = conn.createStatement();
			int start=(page-1)*size;
			int limit=size;
			//查询SQL
			String selectSql="select * from t_area where 1=1";
			//模糊查询区域名称
			if(form!=null&&form.getArea_name()!=null&&!"".equals(form.getArea_name())){
				selectSql +=" and area_name like'%"+form.getArea_name()+"%'";
			}
			//统计SQL
			String countSql ="select count(*)as c from ("+selectSql+") as t1";
			//分页SQL
			 String pageSql=selectSql+" limit "+start+","+limit;
			 rs= stmt.executeQuery(countSql);
			 int total=0;
			 while(rs.next()){
				 total=rs.getInt("c");
			 }
			 areas.setTotal(total);
			 //查询分页信息
			 rs= stmt.executeQuery(pageSql);
			 while(rs.next()){
			
				  String area_no=rs.getString("area_no");
				  String area_name = rs.getString("area_name");
				  int area_level = rs.getInt("area_level");
				  Area area = new Area(area_no, area_name, area_level);
				  areas.getDataSets().add(area);
			 }
			 areas.init();
			// 5:关闭资源
		} catch (SQLException e) {
			e.printStackTrace();
			throw new Exception("Exception_bean:" +e); 
		} finally {
			jdbcUtils.close(conn, stmt);
		}
		return areas;

	}
	


   //get、set方法
	public String getArea_no() {
		return area_no;
	}
	public void setArea_no(String area_no) {
		this.area_no = area_no;
	}

	public String getArea_name() {
		return area_name;
	}



	public void setArea_name(String area_name) {
		this.area_name = area_name;
	}



	public int getArea_level() {
		return area_level;
	}



	public void setArea_level(int area_level) {
		this.area_level = area_level;
	}
	
	
   

}
